package com.wiseman.jxgl;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper implements ConstValue {

	private static final int DATABASE_VERSION = 5;
	static public String _ID = "_id";

	static String mSelectionByName = STU_NAME + " = ?";

	static public String TABLENAME_STU = "student";
	static public String TABLENAME_COURSE = "course";
	static public String TABLENAME_SN = "serial";

	private static final String CREATE_STUDENT_TABLE = "create table "
			+ TABLENAME_STU + " (" + _ID
			+ " integer primary key autoincrement, " + STU_NAME
			+ " text not null, " + STU_SEX + " boolean, " + STU_TEL + " text, "
			+ STU_ADDR + " text, " + STU_SUB1 + " boolean, " + STU_SUB2
			+ " boolean, " + STU_SUB3 + " boolean, " + STU_SUB4 + " boolean, "
			+ STU_STARTDATE + " text, " + STU_ENDDATE + " text, " + _MEMO
			+ " text )";

	private static final String CREATE_COURSE_TABLE = "create table "
			+ TABLENAME_COURSE + " (" + _ID
			+ " integer primary key autoincrement, " + COURSE_DATE
			+ " text not null, " + COURSE_STAGE + " boolean, " + COURSE_S[0]
			+ " text, " + COURSE_S[1] + " text, " + COURSE_S[2] + " text, "
			+ COURSE_S[3] + " text, " + _MEMO + " text )";
	
	
	private static final String CREATE_SERIAL_TABLE = "create table "
			+ TABLENAME_SN + " (" + SERIAL_NUMBER + " text)";
	
	private static final String ALTER_COURSE_TABLE2 = "alter table "
			+ TABLENAME_COURSE + " add " + COURSE_C + " text";

	private static final String ALTER_STUDENT = "alter table " + TABLENAME_STU
			+ " add " + STU_IMAGE + " text";

	private static final String ALTER_STUDENT_REGION = "alter table "
			+ TABLENAME_STU + " add " + STU_REGION + " boolean";

	private static final String ALTER_COURSE_TABLE_ADDEXTRASTU = "alter table "
			+ TABLENAME_COURSE + " add " + COURSE_EXTRASTU + " text";

	public static DatabaseHelper createInstance(Context context) {
		if (s_Instance == null) {
			s_Instance = new DatabaseHelper(context);
		}

		return s_Instance;
	}

	static public DatabaseHelper Instance() {
		return s_Instance;
	}

	static public void DeleteInstance() {
		if (s_Instance != null) {
			s_Instance.mDatabase.close();
			s_Instance.mDatabase = null;

		}
		s_Instance.close();
		s_Instance = null;
	}

	private DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		s_Instance = this;
		mDatabase = getWritableDatabase();

	}

	private static DatabaseHelper s_Instance = null;
	private SQLiteDatabase mDatabase;

	@Override
	public void onCreate(SQLiteDatabase db) {

		db.execSQL(CREATE_STUDENT_TABLE);
		Log.i("jxgl", "database version:" + db.getVersion());
		if (db.getVersion() < DATABASE_VERSION) {
			onUpgrade(db, 0, DATABASE_VERSION);
			db.setVersion(DATABASE_VERSION);
		}

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (oldVersion < 2) {
			db.execSQL(ALTER_STUDENT);
			db.execSQL(CREATE_COURSE_TABLE);
		}

		if (oldVersion < 3) {
			db.execSQL(ALTER_COURSE_TABLE2);
		}

		if (oldVersion < 4) {
			db.execSQL(ALTER_STUDENT_REGION);
		}

		if (oldVersion < 5) {
			db.execSQL(ALTER_COURSE_TABLE_ADDEXTRASTU);
			db.execSQL(CREATE_SERIAL_TABLE);
		}

	}

	Cursor getStudents(String selection) {
		return mDatabase.query(TABLENAME_STU, null, selection, null, null,
				null, STU_STARTDATE + " desc, " + STU_NAME);
	}
		
	public long addStudent(String name, String tel) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(STU_NAME, name);
		initialValues.put(STU_TEL, tel);

		return mDatabase.insert(TABLENAME_STU, null, initialValues);
	}

	public long insertStudent(Student stu) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(STU_NAME, stu.name);
		initialValues.put(STU_SEX, stu.sex);
		initialValues.put(STU_TEL, stu.tel);
		initialValues.put(STU_ADDR, stu.address);
		initialValues.put(STU_SUB1, stu.sub1);
		initialValues.put(STU_SUB2, stu.sub2);
		initialValues.put(STU_SUB3, stu.sub3);
		initialValues.put(STU_SUB4, stu.sub4);
		initialValues.put(STU_STARTDATE, stu.startDate);
		initialValues.put(STU_ENDDATE, stu.endDate);
		initialValues.put(_MEMO, stu.memo);
		initialValues.put(STU_IMAGE, stu.imagePath);
		initialValues.put(STU_REGION, stu.mIsLocal ? 0 : 1);

		Cursor cursor = mDatabase.query(TABLENAME_STU, null, mSelectionByName,
				new String[] { stu.name }, null, null, null);
		if (cursor.moveToNext()) {
			return mDatabase.update(TABLENAME_STU, initialValues,
					mSelectionByName, new String[] { stu.name });
		} else {
			return mDatabase.insert(TABLENAME_STU, null, initialValues);
		}

	}

	int deleteStudentByName(String name) {
		return mDatabase.delete(TABLENAME_STU, mSelectionByName,
				new String[] { name });
	}

	public Student getStudentByName(String name) {
		Student s = null;
		Cursor cursor = mDatabase.query(TABLENAME_STU, null, mSelectionByName,
				new String[] { name }, null, null, null);

		if (cursor.moveToNext()) {
			s = new Student();
			s.name = cursor.getString(cursor.getColumnIndex(STU_NAME));
			s.sex = cursor.getInt(cursor.getColumnIndex(STU_SEX)) != 0 ? true
					: false;
			s.tel = cursor.getString(cursor.getColumnIndex(STU_TEL));
			s.address = cursor.getString(cursor.getColumnIndex(STU_ADDR));
			s.sub1 = cursor.getInt(cursor.getColumnIndex(STU_SUB1)) != 0 ? true
					: false;
			s.sub2 = cursor.getInt(cursor.getColumnIndex(STU_SUB2)) != 0 ? true
					: false;
			s.sub3 = cursor.getInt(cursor.getColumnIndex(STU_SUB3)) != 0 ? true
					: false;
			s.sub4 = cursor.getInt(cursor.getColumnIndex(STU_SUB4)) != 0 ? true
					: false;
			s.startDate = cursor
					.getString(cursor.getColumnIndex(STU_STARTDATE));
			s.endDate = cursor.getString(cursor.getColumnIndex(STU_ENDDATE));
			s.memo = cursor.getString(cursor.getColumnIndex(_MEMO));
			s.imagePath = cursor.getString(cursor.getColumnIndex(STU_IMAGE));
			s.mIsLocal = cursor.getInt(cursor.getColumnIndex(STU_REGION)) == 0 ? true
					: false;
		}

		return s;
	}

	Cursor getCourses(String selection) {
		return mDatabase.query(TABLENAME_COURSE, null, selection, null, null,
				null, COURSE_DATE + " desc, " + COURSE_STAGE + " desc");
	}

	public Course getCoursesByDate(String date, String stage) {
		Course c = null;
		String selectionClause = COURSE_DATE + "= " + "\"" + date + "\""
				+ " and " + COURSE_STAGE + "= " + "\"" + stage + "\"";

		Cursor cursor = mDatabase.query(TABLENAME_COURSE, null,
				selectionClause, null, null, null, null);

		if (cursor.moveToNext()) {
			c = new Course();
			c.date = cursor.getString(cursor.getColumnIndex(COURSE_DATE));
			c.stage = cursor.getString(cursor.getColumnIndex(COURSE_STAGE));
			c.coach = cursor.getString(cursor.getColumnIndex(COURSE_C));
			for (String course_s : COURSE_S) {
				String s = cursor.getString(cursor.getColumnIndex(course_s));
				if (s != null && s.isEmpty() == false) {
					c.students.add(s);
				}
			}

			String extra = cursor.getString(cursor.getColumnIndex(COURSE_EXTRASTU));
			
			c.getExtraStudentList(extra);
		
			c.memo = cursor.getString(cursor.getColumnIndex(_MEMO));

		}

		return c;
	}

	public long insertCourse(Course c) {

		String selectionClause = COURSE_DATE + "= " + "\"" + c.date + "\""
				+ " and " + COURSE_STAGE + "= " + "\"" + c.stage + "\"";

		ContentValues initialValues = new ContentValues();
		initialValues.put(COURSE_DATE, c.date);
		initialValues.put(COURSE_STAGE, c.stage);
		initialValues.put(COURSE_C, c.coach);
		
		//clear it first.
		for (int i = 0; i < COURSE_S.length; i++) {
			initialValues.put(COURSE_S[i], (String)null);
		}

		for (int i = 0; i < c.students.size(); i++) {
			initialValues.put(COURSE_S[i], c.students.get(i));
		}

				
		initialValues.put(COURSE_EXTRASTU, c.getExtraStudentString());

		initialValues.put(_MEMO, c.memo);

		Cursor cursor = mDatabase.query(TABLENAME_COURSE, null,
				selectionClause, null, null, null, null);
		if (cursor.moveToNext()) {
			return mDatabase.update(TABLENAME_COURSE, initialValues,
					selectionClause, null);
		} else {
			return mDatabase.insert(TABLENAME_COURSE, null, initialValues);
		}

	}

	int deleteCourseByName(String date, String stage) {
		String selectionClause = COURSE_DATE + "= " + "\"" + date + "\""
				+ " and " + COURSE_STAGE + "= " + "\"" + stage + "\"";

		return mDatabase.delete(TABLENAME_COURSE, selectionClause, null);
	}

	public ArrayList<String> queryCourseInfoByName(String name) {

		String today = Util.getToday();
		ArrayList<String> infos = new ArrayList<String>();
		Cursor cursor = mDatabase.query(TABLENAME_COURSE, null, null, null,
				null, null, null);
		while (cursor.moveToNext()) {
			Course c = new Course();
			c.date = cursor.getString(cursor.getColumnIndex(COURSE_DATE));
			c.stage = cursor.getString(cursor.getColumnIndex(COURSE_STAGE));
			c.coach = cursor.getString(cursor.getColumnIndex(COURSE_C));

			if (today.compareTo(c.date) < 0)
				continue;

			for (String course_s : COURSE_S) {
				String s = cursor.getString(cursor.getColumnIndex(course_s));
				if (s != null && s.equals(name)) {
					infos.add(c.date + c.stage + "\r\n");
				}
			}

			String extra = cursor.getString(cursor
					.getColumnIndex(COURSE_EXTRASTU));
			c.getExtraStudentList(extra);
			for (String s : c.extra_students) {
				if (s.equals(name)) {
					infos.add(c.date + c.stage + "\r\n");
				}
			}

		}

		return infos;

	}

	public int[] getStudentStageInfo() {

		int stage[] = new int[STU_STAGE_NUM];
		String selection[] = { STU_SUB1 + "= 0",
				STU_SUB1 + "= 1" + " and " + STU_SUB2 + "= 0",
				STU_SUB2 + "= 1" + " and " + STU_SUB3 + "= 0",
				STU_SUB3 + "= 1" + " and " + STU_SUB4 + "= 0",
				STU_SUB4 + "= 1", };

		Cursor cursor = null;
		for (int i = 0; i < STU_STAGE_NUM; i++) {
			cursor = mDatabase.query(TABLENAME_STU, null, selection[i], null,
					null, null, null);

			stage[i] = cursor.getCount();
		}

		return stage;
	}

	public ArrayList<Date> getStudentRecruitInfo() {

		Cursor cursor = mDatabase.query(TABLENAME_STU,
				new String[] { STU_STARTDATE }, null, null, null, null,
				STU_STARTDATE);

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		ArrayList<Date> dateList = new ArrayList<Date>();
		while (cursor.moveToNext()) {
			String dstr = cursor
					.getString(cursor.getColumnIndex(STU_STARTDATE));
			try {
				Date date = sdf.parse(dstr);
				dateList.add(date);
			} catch (ParseException e) {
				Log.d("jxgl", "Invaild date :" + dstr);
				e.printStackTrace();
			}
		}

		return dateList;
	}
	
	public String getSerialNumber(){
		String sn = null;
		
		Cursor cursor = mDatabase.query(TABLENAME_SN,
				new String[] {SERIAL_NUMBER }, null, null, null, null,	null);
		
		if(cursor.moveToNext()){
			sn = cursor.getString(cursor.getColumnIndex(SERIAL_NUMBER));
		}			
		
		return sn;
	}
	
	public void setSerialNumber(String sn){
		
		
		ContentValues initialValues = new ContentValues();
		initialValues.put(SERIAL_NUMBER, sn);
		
		mDatabase.insert(TABLENAME_SN, null, initialValues);		
	}
	

}
